﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace ZHDJ.Data.Models.Center
{
    /// <summary>
    /// 组织关系转移
    /// </summary>
    public class PartyRelationshipTransfer : EntityBase
    {
        #region 字段属性

        /// <summary>
        /// 申请内容
        /// </summary>
        public string Explain { get; set; }

        /// <summary>
        /// 目标组织
        /// </summary>
        public string TageBranchID { get; set; }

        /// <summary>
        /// 申请人组织
        /// </summary>
        public string FormerBranchID { get; set; }

        /// <summary>
        /// 申请人
        /// </summary>
        public string Applicant { get; set; }

        /// <summary>
        /// CreateTime
        /// </summary>
        public DateTime CreateTime { get; set; }

        /// <summary>
        /// 0进行中，1完成
        /// </summary>
        public string Status { get; set; }

        /// <summary>
        /// 编号
        /// </summary>
        public string SerialNumber { get; set; }

        /// <summary>
        /// 年龄
        /// </summary>
        public int Age { get; set; }

        /// <summary>
        /// 所属组织联系电话
        /// </summary>
        public string FromBranchMobileNo { get; set; }

        /// <summary>
        /// 所属组织联系传真
        /// </summary>
        public string FromBranchFax { get; set; }

        /// <summary>
        /// 所属组织邮编
        /// </summary>
        public string FromBranchZip { get; set; }


        /// <summary>
        /// 所属组织联系地址
        /// </summary>
        public string FromBranchAddress { get; set; }


        /// <summary>
        /// 党费缴纳日期
        /// </summary>
        public string MemberCostTime { get; set; }

        /// <summary>
        /// 有效天数
        /// </summary>
        public int ValidTime { get; set; }

        /// <summary>
        /// 转移类型  0区内 1 向外 2向内
        /// </summary>
        public int? Type { get; set; }

        #endregion

        #region 查询管理
        public static DataSet GetGridData(string id)
        {
            StringBuilder sbSql = new StringBuilder();
            List<MySqlParameter> parameters = new List<MySqlParameter>();

            sbSql.Append(@" select a.*,b.UserName,c.BranchName as TageBranchName,d.BranchName as FromBranchName
from tblPartyRelationshipTransfer as a
left join tblPartyOrgMember as b on b.LoginID = a.Applicant
left join tblPartyOrgBranch as c on c.BranchID = a.TageBranchID
left join tblPartyOrgBranch as d on d.BranchID = a.FormerBranchID where a.ID =?ID");

            parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "ID", Value = string.Format("{0}", id) });
            return MySqlHelper.ExecuteDataSet(sbSql.ToString(), parameters.ToArray());
        }

        public static DataSet GetBranchGridData(int pageIndex, int pageSize, string branchId)
        {
            StringBuilder sbSql = new StringBuilder();

            sbSql.Append(@"select SQL_CALC_FOUND_ROWS a.*,b.UserName,c.Title,c.TaskUrl,c.ToUser from tblpartyrelationshiptransfer as a
                           left join tblpartyorgmember as b on b.LoginID = a.Applicant 
                           left join tblpartytask as c on c.RelationId = a.ID and c.CreateTime in (select MAX(d.CreateTime) from tblpartytask d where d.RelationId = a.ID)
                           where 1=1");

            if(!string.IsNullOrEmpty(branchId))
            {
                var sql = "select ParentBranchID from tblpartyorgbranch where BranchID = '" + branchId + "'";
                var parentbranchId = MySqlHelper.ExecuteScalar(sql);
                if (parentbranchId.ToString() == "D658070EC3BF425BA8417745FDCBFCB0")//如果上级部门为山海天,则查找部门底下多有党委的信息
                {
                    sbSql.Append(@" and (TageBranchID in (select BranchID from tblpartyorgbranch where ParentBranchID= ?TageBranchID)
                                or FormerBranchID in (select BranchID from tblpartyorgbranch where ParentBranchID=?TageBranchID) or TageBranchID=?TageBranchID or FormerBranchID=?TageBranchID)");
                }
                else
                {
                    sbSql.Append(" and (TageBranchID=?TageBranchID or FormerBranchID=?TageBranchID)");
                }
            }

            sbSql.Append(" order by a.CreateTime desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");

            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "TageBranchID",
                    Value = branchId
                },
                new MySqlParameter()
                {
                    DbType = DbType.Int32,
                    ParameterName = "Begin",
                    Value = (pageIndex)*pageSize
                },
                new MySqlParameter()
                {
                    DbType = DbType.Int32,
                    ParameterName = "PageSize",
                    Value = pageSize
                }
            };

            return MySqlHelper.ExecuteDataSet(sbSql.ToString(), parameters.ToArray());
        }

        /// <summary>
        /// 获取正在审批中的数据
        /// </summary>
        /// <param name="loginID"></param>
        /// <returns></returns>
        public static DataSet GetApplying(string loginID)
        {
            StringBuilder sbSql = new StringBuilder();
            List<MySqlParameter> parameters = new List<MySqlParameter>();

            sbSql.Append(@"select * from tblPartyRelationshipTransfer where Applicant=?Applicant and Status='审批中'");
            parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "Applicant", Value = string.Format("{0}", loginID) });

            return MySqlHelper.ExecuteDataSet(sbSql.ToString(), parameters.ToArray());
        }

        /// <summary>
        /// 获取序列号
        /// </summary>
        /// <returns></returns>
        public static string GetSerialNumber()
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(@"  select Count(1) from tblPartyRelationshipTransfer ");
            object obj = MySqlHelper.ExecuteScalar(sbSql.ToString());
            if (obj != null && obj.ToString() != "")
                return (Convert.ToInt32(obj) + 1).ToString("D8");
            else
                return "00000001";
        }

        public static bool CheckSerialNumber(string serialNumber)
        {
            StringBuilder sbSql = new StringBuilder();
            List<MySqlParameter> parameters = new List<MySqlParameter>();

            sbSql.Append(@"  select COUNT(*) from tblPartyRelationshipTransfer where SerialNumber=?SerialNumber ");
            parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "SerialNumber", Value = string.Format("{0}", serialNumber) });
            return Convert.ToInt32(MySqlHelper.ExecuteScalar(sbSql.ToString(), parameters.ToArray())) > 0;
        }

        public static string GetMemberCostDate(string loginID)
        {
            StringBuilder sbSql = new StringBuilder();
            List<MySqlParameter> parameters = new List<MySqlParameter>();

            sbSql.Append(@"select CONCAT(YEAR,'-',Month) as PayDate from tblPartyMemberCost cost
inner join tblPartyOrgMember member on cost.LoginID=member.LoginID
where PayDate is not null and cost.LoginID=?LoginID
order by YEAR desc ,Month desc LIMIT 0,1");
            parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "LoginID", Value = string.Format("{0}", loginID) });

            object obj = MySqlHelper.ExecuteScalar(sbSql.ToString(), parameters.ToArray());
            if (obj != null && obj.ToString() != "")
                return Convert.ToDateTime(obj).ToString("yyyy-MM");
            else
                return "";
        }

        public static void AddRelationshipBranch(string id, int type)
        {
            StringBuilder sbSql = new StringBuilder();
            List<MySqlParameter> parameters = new List<MySqlParameter>();
            switch (type)
            {
                case 0:
            sbSql.Append(@" call sp_RelationshipTransfer_Branch (?ID) ");
                    break;
                case 1:
                    sbSql.Append(@" call sp_RelationshipTransfer_BranchToOut (?ID) ");
                    break;
                case 2:
                    sbSql.Append(@" call sp_RelationshipTransfer_BranchToIn (?ID) ");
                    break;
            }
            parameters.Add(new MySqlParameter() {DbType = DbType.String, ParameterName = "ID", Value = id});
            MySqlHelper.ExecuteNonQuery(sbSql.ToString(), parameters.ToArray());
        }

        public static DataTable GetProcessesApproval(string id)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>();
            string sql = @" select a.*,b.UserName,b.MobileNo from tblpartyrelationshipprocesses as a,tblpartyorgmember as b 
where a.Approver = b.LoginID and ApplyID=?ApplyID order by CreateTime ";
            parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "ApplyID", Value = string.Format("{0}", id) });

            return MySqlHelper.ExecuteDataTable(sql, parameters.ToArray());
        }

        public static DataTable GetProcessesView(string id)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>();
            string sql = @"drop table if EXISTS tmp_tb; 
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_tb 
                          (  
						       ID	VARCHAR(32),
						       ApplyID VARCHAR(32),
						       Approver	VARCHAR(128),
                               `Explain` LONGTEXT,
                               ApprovalResult VARCHAR(32),
                               StepInfo INT,
                               CreateTime datetime,
                               UserName VARCHAR(50),
                               MobileNo VARCHAR(50),
                               BranchName VARCHAR(50)
                          );  
                          TRUNCATE TABLE tmp_tb;
                          INSERT INTO tmp_tb SELECT a.*,b.UserName,b.MobileNo, '' FROM tblpartyrelationshipprocesses a,tblpartyorgmember b 
                          WHERE a.Approver=b.LoginID AND ApplyID=?ApplyID ORDER BY CreateTime;

                          UPDATE tmp_tb AS a,tblPartyRelationshipTageBranch AS b,tblpartyorgbranch AS c
                          SET a.BranchName=ifnull(c.BranchName,'无') 
                          WHERE c.BranchID=b.TageBranchid AND a.StepInfo=b.OrderNo+1 AND a.ApplyID=b.ApplyID;
                          SELECT * FROM tmp_tb;";

            parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "ApplyID", Value = string.Format("{0}", id) });
            return MySqlHelper.ExecuteDataTable(sql, parameters.ToArray());
        }

        public static DataSet GetApplyAll(string loginID)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>();
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(@"  select data.*,ifnull(f.BranchName,data.FormerBranchID) as FromBranchName,ifnull(t.BranchName,data.TageBranchID) as ToBranchName from tblPartyRelationshipTransfer data 
                                left join tblPartyOrgBranch f on (f.BranchID=data.FormerBranchID )
                                left join tblPartyOrgBranch t on (t.BranchID=data.TageBranchID )
                where data.Applicant = ?Applicant
                order by CreateTime desc ");
            parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "Applicant", Value = string.Format("{0}", loginID) });
            return MySqlHelper.ExecuteDataSet(sbSql.ToString(), parameters.ToArray());
        }

        /// <summary>
        /// 最后一步步骤信息
        /// </summary>
        /// <param name="applyID"></param>
        /// <param name="branchID"></param>
        /// <returns></returns>
        public static DataTable GetCurrentStep(string applyID, string branchID)
        {
            StringBuilder sbSql = new StringBuilder();
            List<MySqlParameter> parameters = new List<MySqlParameter>();

            sbSql.Append(@"select * from tblPartyRelationshipTageBranch where ApplyID=?ApplyID and TageBranchID=?TageBranchID");
            parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "ApplyID", Value = string.Format("{0}", applyID) });
            parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "TageBranchID", Value = string.Format("{0}", branchID) });
            return MySqlHelper.ExecuteDataTable(sbSql.ToString(), parameters.ToArray());
        }

        public static DataTable GetRelationshipTageBranch(string id, int stepCount)
        {
            StringBuilder sbSql = new StringBuilder();
            List<MySqlParameter> parameters = new List<MySqlParameter>();

            sbSql.Append(@"  select * from tblPartyRelationshipTageBranch where 1 = 1  and  ApplyID = ?ApplyID and OrderNO = ?OrderNO");
            parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "ApplyID", Value = string.Format("{0}", id) });
            parameters.Add(new MySqlParameter() { DbType = DbType.Int32, ParameterName = "OrderNO", Value = string.Format("{0}", stepCount) });
            
            return MySqlHelper.ExecuteDataTable(sbSql.ToString(), parameters.ToArray());
        }
        #endregion
    }

}